﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Db2Model
{
    public class DbHelper
    {
        #region GetDbTables

        public static List<DbTable> GetDbTables(string connectionString, string database, string tables = null)
        {

            if (!string.IsNullOrEmpty(tables))
            {
                tables = string.Format(" and d.name in ('{0}')", tables.Replace(",", "','"));
            }

            #region SQL
            #region 2000
            //            string sql = string.Format(@"SELECT
            //   tablename = CASE WHEN a.colorder = 1 THEN d.name
            //             ELSE ''
            //        END ,
            //   tabledesc = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
            //              ELSE ''
            //         END
            //FROM
            //   syscolumns a
            //   INNER JOIN sysobjects d ON a.id = d.id
            //                              AND d.xtype = 'U'
            //                              AND d.name <> 'sys.extended_properties'
            //                              AND d.name <> 'dtproperties'
            //   LEFT JOIN sysproperties f ON a.id = f.id
            //                                AND f.smallid = 0
            //WHERE
            //   ( CASE WHEN a.colorder = 1 THEN d.name
            //          ELSE ''
            //     END ) <> '' {0}
            //ORDER BY
            //   tablename", tables);
            #endregion

            string sql = string.Format(@"SELECT
   tablename = CASE WHEN a.colorder = 1 THEN d.name
             ELSE ''
        END ,
   tabledesc = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
              ELSE ''
         END
FROM
   syscolumns a
   INNER JOIN sysobjects d ON a.id = d.id
                              AND d.xtype = 'U'
                              AND d.name <> 'sys.extended_properties'
                              AND d.name <> 'dtproperties'
                              AND d.name <> 'sysdiagrams'
   LEFT JOIN sys.extended_properties f ON a.id = f.major_id
                                AND f.minor_id = 0
WHERE
   ( CASE WHEN a.colorder = 1 THEN d.name
          ELSE ''
     END ) <> '' {0}
ORDER BY
   tablename", tables);

            #endregion

            DataTable dt = GetDataTable(connectionString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => new DbTable
            {
                TableName = row.Field<string>("tablename"),
                TableDesc = row.Field<string>("tabledesc"),
            }).ToList();
        }

        #endregion

        #region GetDbColumns

        public static List<DbColumn> GetDbColumns(string connectionString, string tableName)
        {
            #region SQL
            #region 2000
            //            string sql = string.Format(@"
            //SELECT 
            // 主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
            //                     SELECT name FROM sysindexes WHERE indid in(
            //                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
            //    字段序号   = a.colorder,
            //    字段名     = a.name,
            //    类型       = b.name,
            //    允许空     = case when a.isnullable=1 then 1 else 0 end,
            //    字段说明   = isnull(g.[value],'')
            //FROM 
            //    syscolumns a
            //left join 
            //    systypes b 
            //on 
            //    a.xusertype=b.xusertype
            //inner join 
            //    sysobjects d 
            //on 
            //    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
            //left join 
            //    syscomments e 
            //on 
            //    a.cdefault=e.id
            //left join 
            //    sysproperties g 
            //on 
            //    a.id=g.id and a.colid=g.smallid  
            //left join 
            //    sysproperties f 
            //on 
            //    d.id=f.id and f.smallid=0
            //where 
            //    d.name='{0}'    
            //order by 
            //    a.id,a.colorder", tableName);
            #endregion

            string sql = string.Format(@"
SELECT 
 主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
    字段序号   = a.colorder,
    字段名     = a.name,
    类型       = b.name,
    允许空     = case when a.isnullable=1 then 1 else 0 end,
    字段说明   = isnull(g.[value],'')
FROM 
    syscolumns a
left join 
    systypes b 
on 
    a.xusertype=b.xusertype
inner join 
    sysobjects d 
on 
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join 
    syscomments e 
on 
    a.cdefault=e.id
left join 
    sys.extended_properties g 
on 
    a.id=g.major_id and a.colid=g.minor_id  
left join 
    sys.extended_properties f 
on 
    d.id=f.major_id and f.minor_id=0
where 
    d.name='{0}'    
order by 
    a.id,a.colorder", tableName);

            #endregion

            DataTable dt = GetDataTable(connectionString, sql, null);
            return dt.Rows.Cast<DataRow>().Select(row => new DbColumn()
            {
                ColumnID = Convert.ToInt32(row["字段序号"]),
                ColumnName = row["字段名"].ToString(),
                ColumnType = row["类型"].ToString(),
                IsNullable = Convert.ToInt32(row["允许空"]) == 1 ? true : false,
                IsPrimaryKey = Convert.ToInt32(row["主键"]) == 1 ? true : false,
                Remark = row["字段说明"].ToString()
            }).ToList();
        }

        #endregion


        #region GetDataTable

        public static DataTable GetDataTable(string connectionString, string commandText, params SqlParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                if (parms != null && parms.Length > 0)
                    command.Parameters.AddRange(parms);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
        }

        #endregion
    }

    #region DbTable

    /// <summary>
    /// 表结构
    /// </summary>
    public sealed class DbTable
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get; set; }

        public string TableDesc { get; set; }

        /// <summary>
        /// 表的架构
        /// </summary>
        public string SchemaName { get; set; }

        /// <summary>
        /// 表的记录数
        /// </summary>
        public int Rows { get; set; }

        /// <summary>
        /// 是否含有主键
        /// </summary>
        public bool HasPrimaryKey { get; set; }
    }

    #endregion

    #region DbColumn

    /// <summary>
    /// 表字段结构
    /// </summary>
    public sealed class DbColumn
    {
        /// <summary>
        /// 字段ID
        /// </summary>
        public int ColumnID { get; set; }

        /// <summary>
        /// 是否主键
        /// </summary>
        public bool IsPrimaryKey { get; set; }

        /// <summary>
        /// 字段名称
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// 字段类型
        /// </summary>
        public string ColumnType { get; set; }

        /// <summary>
        /// 数据库类型对应的C#类型
        /// </summary>
        public string CSharpType
        {
            get { return SqlServerDbTypeMap.MapCsharpType(ColumnType); }
        }

        /// <summary>
        /// 
        /// </summary>
        public Type CommonType
        {
            get { return SqlServerDbTypeMap.MapCommonType(ColumnType); }
        }


        /// <summary>
        /// 字符长度
        /// </summary>
        public int CharLength { get; set; }

        /// <summary>
        /// 小数位
        /// </summary>
        public int Scale { get; set; }

        /// <summary>
        /// 是否自增列
        /// </summary>
        public bool IsIdentity { get; set; }

        /// <summary>
        /// 是否允许空
        /// </summary>
        public bool IsNullable { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        public string Remark { get; set; }
    }

    #endregion

    #region SqlServerDbTypeMap

    public class SqlServerDbTypeMap
    {
        public static string MapCsharpType(string dbtype)
        {
            if (string.IsNullOrEmpty(dbtype)) return dbtype;
            dbtype = dbtype.ToLower();
            string csharpType = "object";
            switch (dbtype)
            {
                case "bigint":
                    csharpType = "long";
                    break;
                case "binary":
                    csharpType = "byte[]";
                    break;
                case "bit":
                    csharpType = "bool";
                    break;
                case "char":
                    csharpType = "string";
                    break;
                case "date":
                    csharpType = "DateTime";
                    break;
                case "datetime":
                    csharpType = "DateTime";
                    break;
                case "datetime2":
                    csharpType = "DateTime";
                    break;
                case "datetimeoffset":
                    csharpType = "DateTimeOffset";
                    break;
                case "decimal":
                    csharpType = "decimal";
                    break;
                case "float":
                    csharpType = "double";
                    break;
                case "image":
                    csharpType = "byte[]";
                    break;
                case "int":
                    csharpType = "int";
                    break;
                case "money":
                    csharpType = "decimal";
                    break;
                case "nchar":
                    csharpType = "string";
                    break;
                case "ntext":
                    csharpType = "string";
                    break;
                case "numeric":
                    csharpType = "decimal";
                    break;
                case "nvarchar":
                    csharpType = "string";
                    break;
                case "real":
                    csharpType = "Single";
                    break;
                case "smalldatetime":
                    csharpType = "DateTime";
                    break;
                case "smallint":
                    csharpType = "short";
                    break;
                case "smallmoney":
                    csharpType = "decimal";
                    break;
                case "sql_variant":
                    csharpType = "object";
                    break;
                case "sysname":
                    csharpType = "object";
                    break;
                case "text":
                    csharpType = "string";
                    break;
                case "time":
                    csharpType = "TimeSpan";
                    break;
                case "timestamp":
                    csharpType = "byte[]";
                    break;
                case "tinyint":
                    csharpType = "byte";
                    break;
                case "uniqueidentifier":
                    csharpType = "Guid";
                    break;
                case "varbinary":
                    csharpType = "byte[]";
                    break;
                case "varchar":
                    csharpType = "string";
                    break;
                case "xml":
                    csharpType = "string";
                    break;
                default:
                    csharpType = "object";
                    break;
            }
            return csharpType;
        }

        public static Type MapCommonType(string dbtype)
        {
            if (string.IsNullOrEmpty(dbtype)) return Type.Missing.GetType();
            dbtype = dbtype.ToLower();
            Type commonType = typeof(object);
            switch (dbtype)
            {
                case "bigint":
                    commonType = typeof(long);
                    break;
                case "binary":
                    commonType = typeof(byte[]);
                    break;
                case "bit":
                    commonType = typeof(bool);
                    break;
                case "char":
                    commonType = typeof(string);
                    break;
                case "date":
                    commonType = typeof(DateTime);
                    break;
                case "datetime":
                    commonType = typeof(DateTime);
                    break;
                case "datetime2":
                    commonType = typeof(DateTime);
                    break;
                case "datetimeoffset":
                    commonType = typeof(DateTimeOffset);
                    break;
                case "decimal":
                    commonType = typeof(decimal);
                    break;
                case "float":
                    commonType = typeof(double);
                    break;
                case "image":
                    commonType = typeof(byte[]);
                    break;
                case "int":
                    commonType = typeof(int);
                    break;
                case "money":
                    commonType = typeof(decimal);
                    break;
                case "nchar":
                    commonType = typeof(string);
                    break;
                case "ntext":
                    commonType = typeof(string);
                    break;
                case "numeric":
                    commonType = typeof(decimal);
                    break;
                case "nvarchar":
                    commonType = typeof(string);
                    break;
                case "real":
                    commonType = typeof(Single);
                    break;
                case "smalldatetime":
                    commonType = typeof(DateTime);
                    break;
                case "smallint":
                    commonType = typeof(short);
                    break;
                case "smallmoney":
                    commonType = typeof(decimal);
                    break;
                case "sql_variant":
                    commonType = typeof(object);
                    break;
                case "sysname":
                    commonType = typeof(object);
                    break;
                case "text":
                    commonType = typeof(string);
                    break;
                case "time":
                    commonType = typeof(TimeSpan);
                    break;
                case "timestamp":
                    commonType = typeof(byte[]);
                    break;
                case "tinyint":
                    commonType = typeof(byte);
                    break;
                case "uniqueidentifier":
                    commonType = typeof(Guid);
                    break;
                case "varbinary":
                    commonType = typeof(byte[]);
                    break;
                case "varchar":
                    commonType = typeof(string);
                    break;
                case "xml":
                    commonType = typeof(string);
                    break;
                default:
                    commonType = typeof(object);
                    break;
            }
            return commonType;
        }

        public static string MapGetFuncName(string type)
        {
            string func = "GetObject";

            switch (type)
            {
                case "string":
                    func = "GetString"; break;
                case "int":
                    func = "GetInt32"; break;
                case "bool":
                    func = "GetBoolean"; break;
                case "DateTime":
                    func = "GetDateTime"; break;
                case "decimal":
                    func = "GetDecimal"; break;
                case "double":
                    func = "GetDouble"; break;
            }

            return func;
        }

        public static string MapDbType(string type)
        {
            string func = "Int32";
			
            switch (type)
            {
                case "string":
                    func = "String"; break;
                case "int":
                    func = "Int32"; break;
                case "bool":
                    func = "Boolean"; break;
                case "DateTime":
                    func = "DateTime"; break;
                case "decimal":
                    func = "Decimal"; break;
                case "double":
                    func = "Double"; break;
            }

            return func;
        }
    }

    #endregion


}